package cn.pugle.poi;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * poi处理xlsx excel的工具类. 包括取值和设置 <br>
 * xxx 对日期 科学数字 公式的处理可能有问题 <br>
 *
 * @auther tzp on 2016/7/6.<br>
 */
public class POIUtils {
    private static Logger logger = LoggerFactory.getLogger(POIUtils.class);
    public static final String OutputDateFormat = "yyyy-MM-dd";

    public static void main(String[] args) throws IOException, InvalidFormatException {
        XSSFWorkbook excel = new XSSFWorkbook(new File("C:\\Users\\tzp\\Projects\\CodeAccumulate\\src\\main\\resources", "PoiTest.xlsx"));
        XSSFSheet sheet = excel.getSheetAt(0);
        XSSFRow row0 = sheet.getRow(0);
        XSSFRow row1 = sheet.getRow(1);
        System.out.println(sheet.getSheetName());

        System.out.println("------");
        System.out.println(sheet.getPhysicalNumberOfRows());
        System.out.println(sheet.getFirstRowNum());
        System.out.println(sheet.getLastRowNum());//最后一行的index(0base)

        System.out.println("------");
        System.out.println(row1.getPhysicalNumberOfCells());//实际一共有几个cell， 中间有空的不算
        System.out.println(row1.getFirstCellNum());
        System.out.println(row1.getLastCellNum());//最后一个cell的index(0base) + 1
        System.out.println("------");
        for (int i = row0.getFirstCellNum(); i < row0.getLastCellNum(); i++) {
            XSSFCell titleCell = row0.getCell(i);
            XSSFCell valueCell = row1.getCell(i);
            if (titleCell != null) {
                System.out.print(titleCell.getStringCellValue());
                System.out.print(" : " + valueCell.getCellType());
                System.out.print(" : " + valueCell.getRawValue());
                if (valueCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    System.out.print(" : " + valueCell.getCellFormula());
                    System.out.print(" : " + getSingleCellValue(valueCell));
                    System.out.print(" : " + valueCell.getRawValue());
                    valueCell.setCellFormula("SUM(J3,I3)");
                    System.out.println(" : " + valueCell.getNumericCellValue());
//                    valueCell.setCellValue("SUM(J3,I3)");
//                    System.out.println(valueCell.getStringCellValue());
                } else {
                    System.out.println(" : " + getSingleCellValue(valueCell));
                }
            }
        }
        excel.setForceFormulaRecalculation(true);
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\tzp\\Projects\\CodeAccumulate\\src\\main\\resources", "PoiTest1.xlsx"));
        excel.write(out);
        excel.close();
        XSSFDataFormat dataFormat = excel.createDataFormat();
        short format = dataFormat.getFormat("");
    }

    /**
     * 在指定行列创建cell, 设置类型 style value
     */
    public static void createCell(XSSFRow row, int cellIndex, int cellType, CellStyle cellStyle, String value) {
        XSSFCell cell = row.createCell(cellIndex, cellType);
        cell.setCellStyle(cellStyle);
        setCellValue(cell, value, cellType);
    }

    /**
     * 根据cell的type来设置value, 不改变cellType(cell.setCellValue会改变cellType)
     */
    public static void setCellValue(XSSFCell cell, String value, int cellType) throws NumberFormatException {
        if (value == null) {
            logger.warn("seting a null value to a cell {} , {}", cell.getRowIndex(), cell.getColumnIndex());
            return;
        }
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: {
                try {
                    SimpleDateFormat sdf = new SimpleDateFormat(OutputDateFormat);
                    cell.setCellValue(sdf.parse(value));
                } catch (ParseException e) {
                    cell.setCellValue(Double.parseDouble(value));
                }
                return;
            }
            case Cell.CELL_TYPE_STRING: {
                cell.setCellValue(value);
                return;
            }
            case Cell.CELL_TYPE_BOOLEAN: {
                cell.setCellValue(Boolean.parseBoolean(value));
                return;
            }
            case Cell.CELL_TYPE_FORMULA: {
                cell.setCellFormula(value);
                return;
            }
            default: {
            }
        }
    }

    /**
     * 取得行、列的Cell的值，如果是合并单元格，取各并格的值
     */
    public static String getExcelCellValue(XSSFSheet sheet, int row, int column) {
        return getExcelCellValue(sheet, sheet.getRow(row).getCell(column));
    }

    /**
     * 取得Cell的值，如果是合并单元格，取各并格的值
     */
    public static String getExcelCellValue(XSSFSheet sheet, Cell cell) {
        if (cell == null) {
            return "";
        }
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();
        if (isMergedRegion(sheet, rowIndex, columnIndex)) {
            return getMergedRegionValue(sheet, rowIndex, columnIndex);
        }
        return getSingleCellValue(cell);
    }

    public static String getSingleCellValue(XSSFSheet sheet, int row, int column) {
        return getSingleCellValue(sheet.getRow(row).getCell(column));
    }

    /**
     * 取得Cell的值，考虑单元格各种类型，如字符串，数字，公式等. 不考虑合并格. 已trim
     */
    public static String getSingleCellValue(Cell cell) {
        String ret = "";
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: {
                if (DateUtil.isCellDateFormatted(cell)) {//excel中日期存储Numeric类型
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat sdfDate = new SimpleDateFormat(OutputDateFormat);
                    return sdfDate.format(dt);
                } else {
                    double i = cell.getNumericCellValue();
                    ret = i + "";
                    if (ret.contains(".") && StringUtils.isNumeric(ret.split("\\.")[1])) {
                        if (ret.endsWith(".0")) {
                            ret = ret.replace(".0", "");
                        }
                        return ret;
                    }
                    DecimalFormat df = new DecimalFormat("0");// 使用DecimalFormat类对科学计数法格式的数字进行格式化
                    ret = "" + df.format(i);
                    if (ret.endsWith(".0")) {
                        ret = ret.replace(".0", "");
                    }
                    return ret;
                }
            }
            case Cell.CELL_TYPE_STRING: {
                return cell.getStringCellValue().trim();
            }
            case Cell.CELL_TYPE_BOOLEAN: {
                return cell.getBooleanCellValue() + "";
            }
            case Cell.CELL_TYPE_FORMULA: {
                Double ret2 = cell.getNumericCellValue();
                String temp = ret2.toString();
                if (temp.contains(".")) {
                    ret = temp.substring(0, temp.indexOf("."));
                }
                return ret;
            }
            default: {
                return "";
            }
        }
    }

    /**
     * 判断指定行、列是否是合并单元格
     */
    private static boolean isMergedRegion(XSSFSheet sheet, int row, int column) {
        return getMergeRegionIndex(sheet, row, column) > -1;
    }

    /**
     * 获取指定行、列所在的合并单元格在整个XSSFSheet中的索引， 不属于任何合并单元格则返回-1
     */
    private static int getMergeRegionIndex(XSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn) {
                return i;
            }
        }
        return -1;
    }

    /**
     * 获取指定Cell所在的合并单元格在整个XSSFSheet中的值. 合并单元格第一行第一列的单元格的值作为整个合并区的值
     */
    public static String getMergedRegionValue(XSSFSheet sheet, Cell cell) {
        if (cell == null) {
            return "";
        }
        return getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex());
    }

    /**
     * 获取指定行、列所在的合并单元格在整个XSSFSheet中的值. 合并单元格第一行第一列的单元格的值作为整个合并区的值
     */
    public static String getMergedRegionValue(XSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    XSSFRow fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getSingleCellValue(fCell);
                }
            }
        }
        logger.warn("row:" + row + ", column:" + column + " 不是合并格但作为合并格取值");
        XSSFCell cell = sheet.getRow(row).getCell(column);
        return getSingleCellValue(cell);
    }

}
